﻿<cfsilent>
	<cfscript>
		
		/* 计算全校四级过级率 */
		sql = "SELECT 
				institute_id, institute_name, 
				sbj_id, sbj_name, 
				grade, 
				student_passed, student_taken 
				FROM mv_sbj_grade_cet_status 
				WHERE 
					exam_id = 'CET4' 
					AND 
					student_taken > 5 
				ORDER BY institute_name, sbj_name ";
		
		queryObj = new Query( datasource=application.dnsSlave );

		rs_cet = queryObj.execute( sql=sql ).getResult();

		
		gradeArray = [];
		instituteArray = [];
		subjectArray = [];
		
		gradeList = "";
		
		studentCount = 0;
		studentALLCount = 0;
		
		summarySet = structNew();

		for ( a=1; a LTE rs_cet.recordCount; a++ ) {
			
			if ( not listFind( gradeList, rs_cet["grade"][a], "," ) ) {
				gradeList = listAppend( gradeList, rs_cet["grade"][a], "," );
			}
			
			if ( not arrayFind( instituteArray, [ rs_cet["institute_id"][a], rs_cet["institute_name"][a], [], 0 ] ) ) {
				arrayAppend( instituteArray, [ rs_cet["institute_id"][a], rs_cet["institute_name"][a], [], 0 ] );
			}
			
			if ( not arrayFind( subjectArray, [ rs_cet["sbj_id"][a], rs_cet["sbj_name"][a], rs_cet["institute_id"][a], 0, 0 ] ) ) {
				arrayAppend( subjectArray, [ rs_cet["sbj_id"][a], rs_cet["sbj_name"][a], rs_cet["institute_id"][a], 0, 0 ] );
			}
			
			/* 通过人数 */
			structInsert( summarySet, "P-" & rs_cet["sbj_id"][a] & "-" & rs_cet["grade"][a], rs_cet["student_passed"][a] );
			/* 参考人数 */
			structInsert( summarySet, "A-" & rs_cet["sbj_id"][a] & "-" & rs_cet["grade"][a], rs_cet["student_taken"][a] );
			
			studentCount += rs_cet["student_passed"][a];
			studentALLCount += rs_cet["student_taken"][a];
			
		}
		
		for ( grade in listToArray( listSort(gradeList, "text", "asc"), "," ) ) {
			arrayAppend( gradeArray, [ grade, 0, 0 ] );
		}
		
		for ( a=1; a LTE rs_cet.recordCount; a++ ) {
			
			for ( i=1; i lte arrayLen(gradeArray); i++ ) {

				if ( gradeArray[i][1] eq rs_cet["grade"][a] ) {
					gradeArray[i][2] += rs_cet["student_passed"][a];
					gradeArray[i][3] += rs_cet["student_taken"][a];
				}
				
			}
			
			for ( i=1; i lte arrayLen(subjectArray); i++ ) {

				if ( subjectArray[i][1] eq rs_cet["sbj_id"][a] ) {
					subjectArray[i][4] += rs_cet["student_passed"][a];
					subjectArray[i][5] += rs_cet["student_taken"][a];
				}
				
			}
			
		}
		
		for ( subject in subjectArray ) {
			for ( i=1; i lte arrayLen(instituteArray); i++ ) {
				
				if ( instituteArray[i][1] eq subject[3] ) {
					
					arrayAppend( instituteArray[i][3], [ subject[1], subject[2], subject[4], subject[5] ] );
					instituteArray[i][4] ++;
				
				}
				
			}
		}
		
	</cfscript>
</cfsilent>

<cfif studentCount>

	<table class="UItable">
		<tr class="cellBorder">
			<td>学院</td>
			<td>专业</td>
			<cfloop array="#gradeArray#" index="gradeSet">
				<td width="50" align="center"><span class="number"><cfoutput>#gradeSet[1]#</cfoutput></span></td>
			</cfloop>
			<td width="60" align="center">合计</td>
		</tr>
		<cfloop array="#instituteArray#" index="dep">
			<cfset shown = false />
			<cfloop array="#dep[3]#" index="subject">
				<tr class="cellBorder">
					<cfif not shown>
						<td rowspan="<cfoutput>#dep[4]#</cfoutput>"><cfoutput>#dep[2]#</cfoutput></td>
						<cfset shown = true />
					</cfif>
					<td><cfoutput>#subject[2]#</cfoutput></td>
					<cfloop array="#gradeArray#" index="gradeSet">
						<td align="center">
							<cfif structKeyExists( summarySet, "P-" & subject[1] & "-" & gradeSet[1] ) and structKeyExists( summarySet, "A-" & subject[1] & "-" & gradeSet[1] ) >
								<cfset percent =  numberFormat( 100 * ( summarySet["P-" & subject[1] & "-" & gradeSet[1]] / summarySet["A-" & subject[1] & "-" & gradeSet[1]] ), "__._" ) />
								<span class="number"><cfoutput>#percent#</cfoutput> %</span>
							</cfif>
						</td>
					</cfloop>
					<td align="center">
						<cfset percent =  numberFormat( 100 * ( subject[3] / subject[4] ), "__._" ) />
						<span class="number"><cfoutput>#percent#</cfoutput> %</span>
					</td>
				</tr>
			</cfloop>
		</cfloop>
		<tr class="cellBorder">
			<td colspan="2">合计</td>
			<cfloop array="#gradeArray#" index="gradeSet">
				<td width="50" align="center">
					<cfset percent =  numberFormat( 100 * ( gradeSet[2] / gradeSet[3] ), "__._" ) />
					<span class="number"><cfoutput>#percent#</cfoutput> %</span>
				</td>
			</cfloop>
			<td align="center">
				<cfset percent =  numberFormat( 100 * ( studentCount / studentAllCount ), "__._" ) />
				<span class="number"><cfoutput>#percent#</cfoutput> %</span>
			</td>
		</tr>
	</table>
</cfif>